﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Data.Objects;
using System.Reflection;
using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.DbExpressionExtensions;
using Mysoft.QuickCode.DAL;
using System.Data;
using Mysoft.Test.Entity;
using Mysoft.QuickCode.Data.Validator;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using Mysoft.QuickCode.Common;
using Mysoft.QuickCode.NPOI.SS.UserModel;
using Mysoft.QuickCode.NPOI.HSSF.UserModel;
using Mysoft.QuickCode.Office.Excel;
using Newtonsoft.Json;
using System.Text.RegularExpressions;
using System.Security.Cryptography;
using Mysoft.QuickCode.Office.Excel.Data;

namespace CSharpProj
{
    class Program
    {
        static void Main(string[] args)
        {

            //            string sql = @"select  a.ZjBudgetDtlGUID ,
            //        a.ZjBudgetGUID ,
            //        a.ItemName ,
            //        a.ItemCode ,
            //        a.ItemCode as id ,
            //        a.ParentCode ,
            //        a.ParentCode as _parentId ,
            //        a.RefItemType ,
            //        a.RefItemName ,
            //        a.RefItemKey ,
            //        isnull(a.PreAmountRefer, 0) PreAmountRefer ,
            //        isnull(a.PreAmount, 0) PreAmount ,
            //        isnull(a.AnnualAmount, 0) AnnualAmount ,
            //        isnull(a.CurAmountRefer, 0) CurAmountRefer ,
            //        isnull(a.CurAmount, 0) CurAmount ,
            //        case when not exists ( select top 1
            //                                        1
            //                               from     zj_ZjBudgetDtl child
            //                               where    child.ParentCode = a.ItemCode
            //                                        and child.ZjBudgetGUID = a.ZjBudgetGUID ) then 1
            //             else 0
            //        end ifEnd
            //from    zj_ZjBudgetDtl a
            //        inner join zj_ZjBudget b on b.ZjBudgetGUID = a.ZjBudgetGUID
            //where   ( 1 = 1 )";

            //            sql = DBPager.CreateSelectCount(sql);
            //            Console.WriteLine(sql);
            //            return;
            //git测试
            //Mysoft.Map.Extensions.Initializer.UnSafeInit("Data Source=DOLLY-PC\\MSSQLSERVER2008;Initial Catalog=dotnet_erp256_szxhj;User ID=sa;Password=95938");
            //Mysoft.Map.Extensions.Initializer.UnSafeInit("Data Source=10.5.10.75\\sql2008r2_sz;Initial Catalog=erp303_szzb_zbjzy;User ID=team_sz;Password=95938");


            #region Linq To CPQuery
            //Guid[] gs = new Guid[] { Guid.NewGuid(), Guid.NewGuid() };
            //Guid g = Guid.NewGuid();

            //CPQuery query = EntityTable<User>.Instance
            //    .Select()
            //    .Where(m => m.UserKind.ToString().Between("1", "100"))
            //    .AsQuery();
            //Console.WriteLine(query);
            //foreach (SqlParameter p in query.Command.Parameters)
            //{
            //    Console.WriteLine(string.Format("{0}={1}", p.ParameterName, p.Value));
            //}

            //UserMNG.UserDAL.GetEntity(Guid.NewGuid());  //通过UserGUID主键，查询用户信息实体对象
            //UserMNG.UserDAL.SaveEntity(new User() { UserGUID = Guid.NewGuid(), UserCode = "duyb" });    //新增或更新用户信息
            //UserMNG.UserDAL.DeleteEntity(Guid.NewGuid());   //通过主键删除用户

            //UserMNG.UserDAL.GetEntity(m => m.UserCode == "duyb");   //查询UserCode="duyb"的用户信息
            //UserMNG.UserDAL.GetEntityList(m => m.UserKind == 1);    //查询UserKind=1的所有用户
            //UserMNG.UserDAL.GetEntityList(m => m.UserKind == 1, 
            //    m => new object[] { m.CreatedOn },
            //    OrderByType.ASC);    //查询UserKind=1的所有用户，并通过CreatedOn字段排序
            //UserMNG.UserDAL.UpdateEntity(m => new User() { UserKind = 2 }, 
            //    m => m.UserKind == 1);  //将UserKind=1的用户类型，全部更新为UserKind=2
            //UserMNG.UserDAL.DeleteEntities(m => m.UserCode.Contains("admin"));  //删除UserCode包含字符串“admin”的用户信息


            ////Guid[] gs = new Guid[] { Guid.NewGuid(), Guid.NewGuid() };
            ////Guid g = Guid.NewGuid();
            ////CPQuery query = EntityTable<User>.Instance.Update(m => new User() { UserKind = 1, UserGUID = gs.First(), BUGUID = g })
            ////    //.Where(m => m.UserName == "duyb" && ((m.UserName.Equals(5) || m.UserKind.Equals(1)) && m.UserGUID.Equals(Guid.NewGuid())))
            ////    //.Where(m => ((m.UserName == "duyb" && m.UserKind == 3 && m.IsMobileUser == 1)
            ////    //    || (m.IsLocked == 0 && m.IsUserChangePWD == 1)))
            ////    //.Where(m => m.UserName == "duyb" && m.UserGUID.In(gs.ToArray()))
            ////    .Where(m => m.UserName == "duyb" && m.IsUserChgQzPwd == 5 && m.UserGUID.Equals(gs.First()) && m.UserKind.ToString().In(new string[0]))
            ////    .AsQuery();
            ////Console.WriteLine(query);
            //object o = typeof(MethodCallExpression).Name;

            //Select Top
            //CPQuery query = EntityTable<User>.Instance
            //    .Select()
            //    .Top(1)
            //    .Where(m => m.UserKind == 1 && m.UserName.Equals("duyb"))   // && m.UserGUID == Guid.NewGuid() && m.UserName == "duyb" && m.DepartmentGUID == Guid.NewGuid())
            //    .AsQuery();
            //Console.WriteLine(query);

            ////Update 字段
            //query = EntityTable<User>.Instance
            //    .Update(m => new User() { UserKind = 1 })
            //    .Where(m => m.UserCode.Equals("duyb"))
            //    .AsQuery();
            //Console.WriteLine(query);

            ////Select In
            //query = EntityTable<User>.Instance
            //    .Select()
            //    .Top(10)
            //    .Where(m => m.UserKind.ToString().In(new string[] { "1", "2" }))
            //    .AsQuery();
            //Console.WriteLine(query);

            ////Delete Like
            //query = EntityTable<User>.Instance
            //    .Delete()
            //    .Where(m => m.UserCode.Contains("duybduyb123123") && m.UserGUID.Equals(Guid.NewGuid()))
            //    .AsQuery();
            //Console.WriteLine(query);
            //string[] ss = (new Guid[] { Guid.NewGuid(), Guid.NewGuid() }).Select(g => g.ToString()).ToArray();
            //List<string> ss1 = (new Guid[] { Guid.NewGuid(), Guid.NewGuid() }).Select(g => g.ToString()).ToList();
            //CPQuery query = EntityTable<User>.Instance.Select(m => new object[] { m.BUGUID }).Where(m => m.CreatedBy.ToString().Equals(ss1.First()))
            //    .AsQuery();
            //Console.WriteLine(query);
            #endregion

            #region InTable 支持Select In超长

            //using (ConnectionScope scope = new ConnectionScope())
            //{
            //    //InTable必须启用ConnectionScope才能使用，因为内部会创建一个临时表，临时表生命周期在Scope范围内。
            //    CPQuery query = EntityTable<User>.Instance
            //        .Select()
            //        .Where(m => m.UserCode.InTable(new string[] { "xwm1", "xwm2" })
            //            && m.UserKind.ToString().InTable(new string[] { "0", "1", "2" })
            //            && m.IsDisabeld.Equals(false))
            //        .AsQuery();
            //    DataTable table = query.FillDataTable();
            //    foreach (DataRow item in table.Rows)
            //    {
            //        Console.WriteLine(item["UserName"]);
            //    }
            //    Console.WriteLine(query);
            //}

            #endregion

            #region BaseDAL

            //UserMNG test = new UserMNG();
            //try
            //{
            //    User u = test.DAL.GetEntity(new Guid("9DA49292-805A-E411-942E-00155D0A440B"));
            //    Console.WriteLine(string.Format("GetEntity：UserName = {0}", u.UserName));

            //    u.UserName = "测试（修改）";
            //    test.DAL.SaveEntity(u);
            //    u = test.DAL.GetEntity(new Guid("9DA49292-805A-E411-942E-00155D0A440B"));
            //    Console.WriteLine(string.Format("SaveEntity（修改）：UserName = {0}", u.UserName));

            //    u.UserName = "测试（新增）";
            //    u.UserCode = "duybtestadd";
            //    u.UserGUID = Guid.NewGuid();
            //    test.DAL.SaveEntity(u);
            //    u = test.DAL.GetEntity(m => m.UserCode.Equals("duybtestadd") && m.UserKind.Equals(1));
            //    Console.WriteLine(string.Format("SaveEntity（新增）：UserName = {0}", u.UserName));

            //    u = test.DAL.GetEntity(m => m.UserCode.Equals("duybtestadd"));
            //    Console.WriteLine(string.Format("GetEntity Linq：UserName = {0}", u.UserName));

            //    u = test.DAL.GetEntity(m => m.UserCode.In(new string[] { "duybtestadd" }));
            //    Console.WriteLine(string.Format("GetEntity Linq In：UserName = {0}", u.UserName));

            //    test.DAL.DeleteEntities(m => m.UserCode.Equals("duybtestadd"));
            //    Console.WriteLine(string.Format("DeleteEntities：{0}", test.DAL.GetEntityList(m => m.UserCode.Equals("duybtestadd")).Count));

            //    test.DAL.DeleteEntities(m => m.UserKind.Equals(1));

            //    test.DAL.GetEntityList(m => m.UserKind.ToString().In(new string[] { "1" }));
            //    //test git
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}

            #endregion

            #region 实体验证

            //ValidationResult vr = DataValidator.Exec(new VModel { Age = 10, Name = "123", Email = "duyb@163.com" });
            //if (!vr.Result)
            //{
            //    Console.WriteLine(vr.ErrorMessage);
            //}
            //else
            //{
            //    Console.WriteLine("验证通过！");
            //}

            //vr = DataValidator.Exec(new VModel { Age = 0, Name = "123", Email = "duyb@163.com" });
            //if (!vr.Result)
            //{
            //    Console.WriteLine(vr.ErrorMessage);
            //}

            //vr = DataValidator.Exec(new VModel { Age = 10, Name = "123123121233123", Email = "duyb@163.com" });
            //if (!vr.Result)
            //{
            //    Console.WriteLine(vr.ErrorMessage);
            //}

            //vr = DataValidator.Exec(new VModel { Age = 10, Name = "123", Email = "duyb163.com" });
            //if (!vr.Result)
            //{
            //    Console.WriteLine(vr.ErrorMessage);
            //}


            #endregion

            #region Mysoft.QuickCode分页支持

            //List<Filter> filters = new List<Filter>();
            //filters.Add(new Filter
            //{
            //    Field = "UserCode",
            //    Value = "123"
            //});
            //OrderBy ob = new OrderBy()
            //{
            //    Fields = new string[] { "UserName" },
            //    OrderType = OrderByType.ASC
            //};

            //DBPager.GetPager<User>("select * from myUser where usercode=1 and username=2 and (1=1)", new Mysoft.QuickCode.Common.Pager()
            //{
            //    OrderBy = string.Join(",", ob.Fields) + " " + ob.OrderType.ToString(),
            //    PageSize = 10,
            //    PageNow = 1
            //});

            #endregion

            #region Excel读写测试

            //string filePath = "D:\\Projs\\GitProjs\\QuickCode\\QuickCode\\CSharpProj\\test\\E.xls";
            //SheetTool sheetTool =new SheetTool(filePath, 0);
            //sheetTool.DataRowBeginIndex = 1;
            //sheetTool.ColumnTools = new SheetColumnTool[] { 
            //    new SheetColumnTool(){
            //        ColumnIndex=0,
            //        ColumnName="用户名",
            //        Validator = new Func<IRow, int, string, string>((row, cellIndex, value)=>
            //        {
            //            if (string.IsNullOrEmpty(value))
            //            {
            //                return string.Format("第{0}行“用户名”不能为空！", row.RowNum + 1);
            //            }
            //            return string.Empty;
            //        })
            //    },
            //    new SheetColumnTool(){
            //        ColumnIndex=1,
            //        ColumnName="用户编码",
            //        Validator = new Func<IRow, int, string, string>((row, cellIndex, value)=>
            //        {
            //            if (string.IsNullOrEmpty(value))
            //            {
            //                return string.Format("第{0}行“用户编码”不能为空！", row.RowNum + 1);
            //            }
            //            return string.Empty;
            //        })
            //    },
            //    new SheetColumnTool(){
            //        ColumnIndex=3,
            //        ColumnName="出生日期",
            //        Validator = new Func<IRow, int, string, string>((row, cellIndex, value)=>
            //        {
            //            if (string.IsNullOrEmpty(value))
            //            {
            //                return string.Format("第{0}行“出生日期”不能为空！", row.RowNum + 1);
            //            }
            //            return string.Empty;
            //        })
            //    }
            //};

            //DataTable table = sheetTool.GetTable();
            //int count = table.Rows.Count;
            //Console.WriteLine(count);
            //Console.WriteLine(sheetTool.GetErrorMessages().FirstOrDefault());


            ///////////////////////////////////
            //// 写Excel
            //MemoryStream stream = new TableTool(table, "测试名称").GetExcelStream();
            //File.WriteAllBytes("d:/123.xls", stream.GetBuffer());
            //stream.Close();
            #endregion


            #region 继承对象转换

            //User u = new User()
            //{
            //    UserName = "duyb"
            //};

            //UA a = ObjectConvert.ToEntity<UA>(u);
            #endregion


            #region 小平台默认值设置

            //User a = new User();
            //a.UserGUID = Guid.NewGuid();
            //a.CreatedOn = null;
            //a.SetPropertyDefaultValue<User>(new List<string>() { "CreatedOn" });
            //a.Update();
            ////object val = null;
            ////val = default(int);
            ////Console.WriteLine(val);
            ////Type t1 = typeof(int?);
            ////Type t2 = typeof(DateTime?);
            ////object v1 = default(int?);
            ////object v2 = default(DateTime?);
            //Type t1 = typeof(Nullable<>);
            //Console.WriteLine(default(bool?));
            //Console.WriteLine(default(int?));
            //Console.WriteLine(default(DateTime?));
            //Console.WriteLine(default(bool));
            //Console.WriteLine(default(int));
            //Console.WriteLine(default(float));
            //Console.WriteLine(default(DateTime));

            #endregion

            #region a.a=a.b, a.a=a.b + 1 语法测试
            ////zjBudgetDAL.UpdateEntity(i => new ZjZjBudget { ReportState = "已上报", ApproveAmount = Convert.ToDecimal(_btm.GetDomain("控股总部审定金额").Value) }, i => i.ZjBudgetGUID == new Guid(BusinessGUID));
            //User u = new User()
            //{
            //    UserName = "duyb",
            //    Age = 10
            //};
            //CPQuery query = EntityTable<User>.Instance
            //    .Update(m => new User()
            //    {
            //        UserName = m.UserName + m.Age / 5
            //    })
            //    .Where(m => m.UserCode == new Nullable<Guid>(Guid.Empty).ToString() + m.Age / 10).AsQuery();
            //Console.WriteLine(query.ToString());
            //foreach (SqlParameter p in query.Command.Parameters)
            //{
            //    Console.WriteLine(p.ParameterName + "=" + p.Value);
            //}

            #endregion

            #region datatable生成excel

            //TableTool tt = new TableTool(null, "excel名称");

            //DataTable table = new DataTable();
            //table.Columns.Add("测试1");
            //table.Columns.Add("测试2");
            //table.Columns.Add("测试3");
            //DataRow row = table.NewRow();
            //row["测试1"] = "1";
            //row["测试2"] = "2";
            //row["测试3"] = "3";
            //table.Rows.Add(row);
            //table.Columns["测试1"].ColumnName = "test1";

            #endregion

            //CPQuery query = EntityTable<User>.Instance.Update(m => new User() { UserName = null }).Where(m => m.UserCode == "admin").AsQuery();
            //Console.WriteLine(query.ToString());
            //foreach (SqlParameter p in query.Command.Parameters)
            //{
            //    Console.WriteLine(p.ParameterName + "=" + p.Value);
            //}

            //            string sql = @"
            //    select *, UserName='555' from myUser where UserGUID=
            //";
            //            MatchCollection colls = Regex.Matches(sql, "@[\\w\\d_]+");
            //            foreach (Match item in colls)
            //            {

            //            }
            //try
            //{
            //    FileStream fileStream = new FileStream(@"D:\GitProjects\QuickCode\QuickCode\CSWeb\tempfiles\2015072322100873693.xls", FileMode.Open, FileAccess.ReadWrite);
            //    IWorkbook workbook = new HSSFWorkbook(fileStream);
            //    ISheet dataST = workbook.GetSheetAt(0);
            //    dataST.GetRow(0).GetCell(2).SetCellFormula("A1+B1");
            //    dataST.GetRow(1).GetCell(0).SetCellFormula("B2+C2");
            //    using (FileStream fs2 = File.Create(@"D:/2.xls"))
            //    {
            //        workbook.Write(fs2);
            //    }
            //}
            //catch (Exception ex)
            //{

            //    throw ex;
            //}


            //string a = "abcdefg";
            //Console.WriteLine(Mysoft.Map.Security.Cryptography.EnCode("0"));  //加密
            //Console.WriteLine(Mysoft.Map.Security.Cryptography.DeCode("0"));  //解密
            //byte[] bytEncodes = MySoft.Cryptography.TripleDESHelper.Encrypt(File.ReadAllBytes(@"D:/QuickCode简介.doc"));
            //File.WriteAllBytes(@"D:/QuickCode简介.doc", bytEncodes);
            //bytEncodes = MySoft.Cryptography.TripleDESHelper.Decrypt(File.ReadAllBytes(@"D:/QuickCode简介.doc"));
            //File.WriteAllBytes(@"D:/QuickCode简介.doc", bytEncodes);

            //decimal? abc = (decimal?)((object)"");



            #region 高级Excel导出测试

            for (int p = 0; p < 2; p++)
            {

                List<List<ExcelCell>> lstTable = new List<List<ExcelCell>>();

                //模拟构建一个矩阵Excel表格
                for (int i = 0; i < 100; i++)
                {
                    List<ExcelCell> row = new List<ExcelCell>();
                    if (i == 0)
                    {
                        for (int j = 1; j < 200; j++)
                        {
                            row.Add(new ExcelCell()
                            {
                                Field = "金额" + j,
                                Text = "金额" + ((j < 3) ? 1 : j)
                            });
                        }
                        row.Add(new ExcelCell()
                        {
                            Field = "合计",
                            Text = "合计"
                        });
                    }
                    else
                    {
                        for (int j = 1; j < 200; j++)
                        {
                            row.Add(new ExcelCell()
                            {
                                Field = "金额" + j,
                                Text = (10 * i + j).ToString(),
                                BackgroundColor = ((10 * i + j) % 10 == 0) ? Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.Red.Index : Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL,
                                DataFormat = "#,##0.00",
                                FontColor = ((10 * i + j) % 10 == 0) ? Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.White.Index : Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL
                            });
                        }
                        row.Add(new ExcelCell()
                        {
                            Field = "合计",
                            Text = "0",
                            DataFormat = "#,##0.00"
                        });
                    }
                    lstTable.Add(row);
                }

                //列公式配置字典
                Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> dict = new Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>>();
                string[] monthHeaders = lstTable.FirstOrDefault().Where(m => m.Field != "合计").Select(m => m.Field).ToArray();
                //月份字段设置为允许编辑
                foreach (string m in monthHeaders)
                {
                    dict.Add(m, (value, row, column, rowIndex) =>
                    {
                        return new ExcelCellSetting()
                        {
                            Locked = false
                        };
                    });
                }
                //总价公式
                dict.Add("合计", (value, row, column, rowIndex) =>
                {
                    string fa = string.Empty;
                    if (monthHeaders.Length == 1)
                    {
                        fa = lstTable.GetExcelCellXY(new ExcelCellFilter
                        {
                            RowIndex = rowIndex,
                            Field = monthHeaders[0]
                        }, "0");
                    }
                    else if (monthHeaders.Length > 1)
                    {
                        //合计=月份SUM
                        fa = "SUM(" + lstTable.GetExcelCellXY(new ExcelCellFilter
                        {
                            RowIndex = rowIndex,
                            Field = monthHeaders[0]
                        }) + ":" + lstTable.GetExcelCellXY(new ExcelCellFilter
                        {
                            RowIndex = rowIndex,
                            Field = monthHeaders[monthHeaders.Length - 2]
                        }) + ")";
                    }
                    column.BackgroundColor = Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.Blue.Index;
                    return new ExcelCellSetting
                    {
                        Formula = fa,
                        Locked = true
                    };
                });

                ExcelCreator.ExportToExcel(lstTable, @"D:/导出测试.xls", new ExportExcelSetting() { ECellable = true, HeaderRowCount = 1 }, dict, ("默认" + p.ToString()), CreateMode.AppendSheet);
                
            }
            #endregion

            #region 导出测试

            #endregion

            //string s = Mysoft.Map.Extensions.Json.JsonHelper.JsonSerialize(new
            //{
            //    UserName = "fsdfsd",
            //    Age = 15
            //});

            Console.ReadKey();
        }
    }

    public class UA : User
    {
        public string ABC { set; get; }
    }

    public class UserMNG
    {
        public static BaseDAL<User> UserDAL = new BaseDAL<User>();
    }


}
